import pandas as pd


def compare_excel(file1, file2):
    # 读取两个Excel文件
    df1 = pd.read_excel(file1, engine='openpyxl')
    df2 = pd.read_excel(file2, engine='openpyxl')

    # 标识数据行位置
    df1['Row'] = df1.index + 2  # 添加行号，从1开始
    df2['Row'] = df2.index + 2  # 添加行号，从1开始

    # 合并两个 DataFrame
    # 合并两个 DataFrame
    df1['Source'] = 'file1'
    df2['Source'] = 'file2'

    combined = pd.concat([df1, df2])

    # 查找删除的行(这里查询的是所有不重复的值 要在这里进行下一步区分)
    deleted = combined[combined.duplicated(subset=df1.columns[:-2], keep=False) == False]
    print(f"deleted中被删除的元素的值为{deleted}")
    deleted_file1 = deleted[deleted['Source'] == 'file1']
    deleted_file2 = deleted[deleted['Source'] == 'file2']
    # 在这里重新识别相同的行
    same_rows = combined[combined.duplicated(subset=df1.columns[:-2],keep=False)]
    same_rows1 = combined[combined["Source"] == "file1"]
    same_rows2 = combined[combined["Source"] == "file2"]
    # 遍历file1
    for index,row in same_rows1:

    print(f"same_rows的值为{same_rows}")
    # 查找新增的行
    same_in_file1 = combined[combined.duplicated(subset=df2.columns[:-2], keep=False) == False]
    print(f"same_in_file1的值为:\n{same_in_file1}")
    new = same_in_file1[same_in_file1['Source'] == 'file2']
    print(f"new的值为:{new}")
    # 如果1中不包含2，则是增加
    # for index in range(len(deleted["Row"])):
    #     new = deleted[deleted["Source"] == "file1" and deleted["Row"][index] not in deleted_file2["Row"]]
    new = deleted[(deleted["Source"] == "file1") & (~deleted["Row"].isin(deleted_file2["Row"]))]
    # 2中不包含1则是被删除
    alDeleted = deleted[(deleted["Source"] == "file2") & (~deleted["Row"].isin(deleted_file1["Row"]))]
    # 查找修改的行
    modified = combined[combined.duplicated(subset=df1.columns[:-2], keep=False) == True]
    modified = deleted[(deleted["Source"] == "file1") & (deleted["Row"].isin(deleted_file2["Row"]))]
    modified1 = deleted[(deleted["Source"] == "file2") & (deleted["Row"].isin(deleted_file1["Row"]))]
    # 仍然需要处理
    print("已删除的行:")
    print(alDeleted[['Row'] + df2.columns[:-2].tolist()])

    print("新增的行:")
    print(new[['Row'] + df2.columns[:-2].tolist()])
    row_excel2 = []
    print("修改的行:")
    for index, row in modified.iterrows():
        orig_row = row['Row']
        orig_source = row['Source']
        # 合并表格1和表格2的内容
        print(f"在文件 {orig_source} 的第 {orig_row} 行被修改。")
    print(modified[["Row"]+df1.columns[:-2].tolist()])
    for index, row in modified1.iterrows():
        orig_row = row['Row']
        orig_source = row['Source']
        # 合并表格1和表格2的内容
        print(f"在文件 {orig_source} 的第 {orig_row} 行被修改。")
    print(modified1[["Row"]+df1.columns[:-2].tolist()])

# 使用示例
if __name__ == "__main__":
    compare_excel('diff1.xlsx', 'diff2.xlsx')
